﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace cmsservice.DAL
{
    public class CompanyIPODAL
    {
        /// <summary>
        /// 查询所有股票相关信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetXGList()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select ci.*,OrgIntro,BusinessMajor,SSTJRName=(SELECT STUFF((SELECT '，'+AgencyName FROM ISSUE_AGENCY WHERE OrgID=t1.OrgID and IssueAgencyType='021001' FOR XML PATH('')), 1, 1, '') FROM ISSUE_AGENCY t1 where IssueAgencyType='021001' and OrgID=ci.OrgID GROUP BY OrgID,IssueAgencyType),ZCXSName=(SELECT STUFF((SELECT '，'+AgencyName FROM ISSUE_AGENCY WHERE OrgID=t1.OrgID and IssueAgencyType='021005' FOR XML PATH('')), 1, 1, '') FROM ISSUE_AGENCY t1 where IssueAgencyType='021005' and OrgID=ci.OrgID GROUP BY OrgID,IssueAgencyType),FZCXSName=(SELECT STUFF((SELECT '，'+AgencyName FROM ISSUE_AGENCY WHERE OrgID=t1.OrgID and IssueAgencyType='021006' FOR XML PATH('')), 1, 1, '') FROM ISSUE_AGENCY t1 where IssueAgencyType='021006' and OrgID=ci.OrgID GROUP BY OrgID,IssueAgencyType) from COMPANY_IPO as ci left join ORGANIZATION as org on ci.OrgID=org.OrgID where ci.IsValid='1' and ci.OLIssueDate>'2007-12-31' ");
            DataSet ds= SqlHelper.ExecuteGetDataSet(DBConn.NewDMSConnstr,CommandType.Text,sql.ToString());
            ds.Tables.AddRange(GetXGList2());
            return ds;
        }

        public DataTable [] GetXGList2()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select distinct SecuID,SecuCode,SecuAbbr,ListedDate,SecuMarket,ClosePrice=(select top 1 ClosePrice from TRADE_DAILY_QUOTE where ListedDate=sec.ListedDate and SecuCode=sec.SecuCode),OpenPrice=(select top 1 OpenPrice from TRADE_DAILY_QUOTE where ListedDate=sec.ListedDate and SecuCode=sec.SecuCode),ChangeRatio=(select top 1 ChangeRatio from TRADE_DAILY_QUOTE where ListedDate=sec.ListedDate and SecuCode=sec.SecuCode),TurnoverRate=(select top 1 TurnoverRate from TRADE_DAILY_QUOTE where ListedDate=sec.ListedDate and SecuCode=sec.SecuCode),JJdate=(select top 1 JJdate from EM_GRAB_XIANSHOUJJ where LiftedSharesType1='首发机构配售股份' and SecCode=sec.SecuCode) from [SECURITY] as sec where sec.SecuMarket in(012001,012002,012003,012015) ");
            sb.Append(" select distinct SecuCode,embi.BlockCode,embi.BlockName from EM_BLOCK_INFO as embi left join EM_BLOCK_STOCK as mebs on embi.BlockCode=mebs.BlockCode where BlockCategory='2002' ");
            sb.Append(" select distinct SecuCode,SecuAbbr,SecuCategory from EM_SD_DAILY where SecuCategory in('3001','3002','3011','3012') ");
            var ds = SqlHelper.ExecuteGetDataSet(DBConn.NewDMSConnstr, CommandType.Text, sb.ToString(), null);
            DataTable [] tbs=new DataTable[3];
            ds.Tables.CopyTo(tbs, 0);
            ds.Tables.Clear();
            for (int i = 0; i < tbs.Length; i++)
            {
                tbs[i].TableName ="Table"+ i.ToString();
            }
            return tbs;
        }
    }
}
